package org.zhang.shortlink.project.dao.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.zhang.shortlink.project.dao.entity.ShortLinkAccessStatsDO;
import org.zhang.shortlink.project.dto.req.ShortLinkStatsReqDTO;

import java.util.List;

/**
 * @author zhang
 * @description 针对表【t_link_access_stats】的数据库操作Mapper
 * @createDate 2024-04-23 16:57:27
 * @Entity org.zhang.shortlink.project.dao.entity.ShortLinkAccessStatsDO
 */
@Mapper
public interface ShortLinkAccessStatsMapper extends BaseMapper<ShortLinkAccessStatsDO> {

    @Insert("""
            INSERT INTO t_link_access_stats (full_short_url,date,pv,uv,uip,hour,weekday,create_time,update_time,del_flag)
            VALUES (#{fullShortUrl}, #{date}, #{pv}, #{uv},#{uip},#{hour},#{weekday},#{createTime},#{updateTime},#{delFlag})
            ON DUPLICATE KEY UPDATE pv  = pv + #{pv},uv  = uv + #{uv},uip = uip + #{uip};
            """)
    void shortLinkStats(ShortLinkAccessStatsDO statsDO);

    /**
     * 根据短链接获取指定日期内基础监控数据
     */
    @Select("SELECT " +
            "    date, " +
            "    SUM(pv) AS pv, " +
            "    SUM(uv) AS uv, " +
            "    SUM(uip) AS uip " +
            "FROM " +
            "    t_link_access_stats " +
            "WHERE " +
            "    full_short_url = #{param.fullShortUrl} " +
            "    AND date BETWEEN #{param.startDate} and #{param.endDate} " +
            "GROUP BY " +
            "    full_short_url, date;")
    List<ShortLinkAccessStatsDO> listStatsByShortLink(@Param("param") ShortLinkStatsReqDTO requestParam);


    /**
     * 根据短链接获取指定日期内小时基础监控数据
     */
    @Select("SELECT " +
            "    hour, " +
            "    SUM(pv) AS pv " +
            "FROM " +
            "    t_link_access_stats " +
            "WHERE " +
            "    full_short_url = #{param.fullShortUrl} " +
            "    AND date BETWEEN #{param.startDate} and #{param.endDate} " +
            "GROUP BY " +
            "    full_short_url, hour;")
    List<ShortLinkAccessStatsDO> listHourStatsByShortLink(@Param("param") ShortLinkStatsReqDTO requestParam);


    /**
     * 根据短链接获取指定日期内小时基础监控数据
     */
    @Select("SELECT " +
            "    weekday, " +
            "    SUM(pv) AS pv " +
            "FROM " +
            "    t_link_access_stats " +
            "WHERE " +
            "    full_short_url = #{param.fullShortUrl} " +
            "    AND date BETWEEN #{param.startDate} and #{param.endDate} " +
            "GROUP BY " +
            "    full_short_url, weekday;")
    List<ShortLinkAccessStatsDO> listWeekdayStatsByShortLink(@Param("param") ShortLinkStatsReqDTO requestParam);

}




